CREATE TABLE MaintenanceWindow
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    MeterID INT NOT NULL REFERENCES Meter(ID),
    StartTime DATETIME,
    EndTime DATETIME
)
GO

INSERT INTO DataOperation(AssemblyName, TypeName, LoadOrder) VALUES('FaultData.dll', 'FaultData.DataOperations.StatisticOperation', 10)
GO

INSERT INTO DataOperation(AssemblyName, TypeName, LoadOrder) VALUES('FaultData.dll', 'FaultData.DataOperations.DataPusherOperation', 11)
GO

CREATE TABLE SnapshotHarmonics
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    EventID INT NOT NULL REFERENCES Event(ID),
    ChannelID INT NOT NULL REFERENCES Channel(ID),
    SpectralData varchar(max) NULL
)
GO

CREATE NONCLUSTERED INDEX IX_SnapshotHarmonics_EventID
ON SnapshotHarmonics(EventID ASC)
GO

INSERT INTO EventType(Name, Description) VALUES ('Snapshot', 'Snapshot')
GO

CREATE TABLE EventStat
(
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    EventID INT NOT NULL REFERENCES Event(ID),
    IMin float NULL,
    IMax float NULL,
    VMin float NULL,
    VMax float NULL,
    I2t float NULL,
    InitialMW float NULL,
    FinalMW float NULL,
	PQViewID int null,
    CONSTRAINT UC_EventStat_EventID UNIQUE(EventID)
)
GO

-- Each user can update this to create their own scalar stat view in openSEE
CREATE VIEW OpenSEEScalarStatView AS
SELECT
    Event.ID AS EventID,
    MeterLocation.Name AS Station,
    Meter.Name AS Meter,
    Line.AssetKey AS Line,
    EventType.Name AS EventType,
    DATEDIFF(MILLISECOND, Event.StartTime, Event.EndTime)/1000.0 AS FileDuration,
    FaultSummary.Distance,
    FaultSummary.DurationCycles,
    (
        SELECT TOP 1
            (1 - Disturbance.PerUnitMagnitude) * 100 AS SagDepth
        FROM
            Disturbance JOIN
            EventType ON
                Disturbance.EventTypeID = EventType.ID AND
                EventType.Name = 'Sag' JOIN
            Phase ON
                Disturbance.PhaseID = Phase.ID AND
                Phase.Name = 'Worst'
        WHERE
            Disturbance.EventID = Event.ID AND
            Disturbance.StartTime <= dbo.AdjustDateTime2(FaultSummary.Inception, FaultSummary.DurationSeconds) AND
            Disturbance.EndTime >= FaultSummary.Inception
    ) AS SagDepth,
    FaultSummary.IsSelectedAlgorithm,
    EventStat.I2t,
    EventStat.VMax,
    EventStat.VMin,
    EventStat.IMax,
    VAN.Mapping AS VAN,
    VBN.Mapping AS VBN,
    VCN.Mapping AS VCN,
    IAN.Mapping AS IAN,
    IBN.Mapping AS IBN,
    ICN.Mapping AS ICN,
    IR.Mapping AS IR
FROM
    Event JOIN
    Meter ON Event.MeterID = Meter.ID JOIN
    MeterLocation ON Meter.MeterLocationID = MeterLocation.ID JOIN
    Line ON Event.LineID = Line.ID JOIN
    EventType ON Event.EventTypeID = EventType.ID LEFT OUTER JOIN
    FaultSummary ON
        Event.ID = FaultSummary.EventID AND
        FaultSummary.IsSelectedAlgorithm <> 0 AND
        FaultSummary.FaultNumber = 1 LEFT OUTER JOIN
    EventStat ON Event.ID = EventStat.EventID LEFT OUTER JOIN
    ChannelDetail VAN ON
        Event.MeterID = VAN.MeterID AND
        Event.LineID = VAN.LineID AND
        VAN.MeasurementType = 'Voltage' AND
        VAN.Phase = 'AN' AND
        VAN.MeasurementCharacteristic = 'Instantaneous' AND
        VAN.SeriesType IN ('Values', 'Instantaneous') LEFT OUTER JOIN
    ChannelDetail VBN ON
        Event.MeterID = VBN.MeterID AND
        Event.LineID = VBN.LineID AND
        VBN.MeasurementType = 'Voltage' AND
        VBN.Phase = 'BN' AND
        VBN.MeasurementCharacteristic = 'Instantaneous' AND
        VBN.SeriesType IN ('Values', 'Instantaneous') LEFT OUTER JOIN
    ChannelDetail VCN ON
        Event.MeterID = VCN.MeterID AND
        Event.LineID = VCN.LineID AND
        VCN.MeasurementType = 'Voltage' AND
        VCN.Phase = 'CN' AND
        VCN.MeasurementCharacteristic = 'Instantaneous' AND
        VCN.SeriesType IN ('Values', 'Instantaneous') LEFT OUTER JOIN
    ChannelDetail IAN ON
        Event.MeterID = IAN.MeterID AND
        Event.LineID = IAN.LineID AND
        IAN.MeasurementType = 'Current' AND
        IAN.Phase = 'AN' AND
        IAN.MeasurementCharacteristic = 'Instantaneous' AND
        IAN.SeriesType IN ('Values', 'Instantaneous') LEFT OUTER JOIN
    ChannelDetail IBN ON
        Event.MeterID = IBN.MeterID AND
        Event.LineID = IBN.LineID AND
        IBN.MeasurementType = 'Current' AND
        IBN.Phase = 'BN' AND
        IBN.MeasurementCharacteristic = 'Instantaneous' AND
        IBN.SeriesType IN ('Values', 'Instantaneous') LEFT OUTER JOIN
    ChannelDetail ICN ON
        Event.MeterID = ICN.MeterID AND
        Event.LineID = ICN.LineID AND
        ICN.MeasurementType = 'Current' AND
        ICN.Phase = 'CN' AND
        ICN.MeasurementCharacteristic = 'Instantaneous' AND
        ICN.SeriesType IN ('Values', 'Instantaneous') LEFT OUTER JOIN
    ChannelDetail IR ON
        Event.MeterID = IR.MeterID AND
        Event.LineID = IR.LineID AND
        IR.MeasurementType = 'Current' AND
        IR.Phase = 'RES' AND
        IR.MeasurementCharacteristic = 'Instantaneous' AND
        IR.SeriesType IN ('Values', 'Instantaneous')
GO

INSERT INTO Setting(Name, Value, DefaultValue) VALUES('FaultLocation.FaultCalculationCycleMethod', 'MaxCurrent', 'MaxCurrent')
GO

INSERT INTO DashSettings (Name, Value, Enabled) VALUES ('EventsChart', 'Snapshot', 1)
GO

INSERT INTO DashSettings (Name, Value, Enabled) VALUES ('EventsChartColors', 'Snapshot,#9db087', 1)
GO